//
//  HMStatusDAL.swift
//  HMWeibo12
//
//  Created by iOS on 16/10/7.
//  Copyright © 2016年 iOS. All rights reserved.
//

import UIKit

/// 加载微博数据 1.数据库加载 2.网络加载
class HMStatusDAL: NSObject {
    // 微博数据超时时间
    private let cacheDataTime: TimeInterval = 60 // 7 * 24 * 60 * 60
    
    /// 清楚数据库缓存
    func clearCacheStatus() -> Void {
        // 计算超时日期
        let overDate = Date(timeIntervalSinceNow: -cacheDataTime)
        // 将时间转成SQLite数据库的时间
        
        let df = DateFormatter()
        df.locale = Locale(identifier: "en")
        // 指定日期格式 2016-10-07 20:05:26
        df.dateFormat = "yyyy-MM-dd HH:mm:ss"
        let timeString = df.string(from: overDate)
//        print("timeString: \(timeString)")
        let sql = "DELETE FROM T_Status WHERE createTime < '\(timeString)';"
        
        HMSQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                try db?.executeUpdate(sql, values: [])
                print("清楚数据库中的微博成功了")
            } catch let error as NSError {
                print("清楚数据库中的微博失败了: \(error)")
            }
        }
    }
    
    
    /*
        1.去数据库中加载 *
        2.如果数据库中有直接返回数据
        3.数据库中没有,发送网络请求
        4.网络请求数据回来,保存到数据库中 *
        5.把网络数据返回回去
     */
    func dalLoadStatus(since_id: Int64, max_id: Int64, completion: @escaping ([[String: Any]]?, Error?) -> Void) -> Void {
        // 1.去数据库中加载
        loadCacheStatus(since_id: since_id, max_id: max_id) { (statuses: [[String : Any]]?, error: Error?) in
            if error != nil {
                print("加载本地微博数据出错了: \(error)")
                completion(nil, error)
                return
            }
            
            if let statuses = statuses, statuses.count > 0 {
                // 2.如果数据库中有直接返回数据
                completion(statuses, nil)
                
                return
            }
            
            // 3.数据库中没有,发送网络请求
            guard let access_token = HMUserAccountVM.shared.userAccount?.access_token else {
                print("access_token 没有值")
                return
            }
            let urlString = "statuses/home_timeline.json"
            // 参数
            var parameters: [String: Any] = [
                "access_token": access_token
            ]
            // 下拉刷新或上拉加载
            if since_id > 0 {
                parameters["since_id"] = since_id
            } else if max_id > 0 {
                parameters["max_id"] = max_id - 1
            }
            
            HMNetworkTool.shared.request(method: .GET, URLString: urlString, parameters: parameters, progress: nil, success: { (_, responseObject) in
                
                if let dict = responseObject as? [String: Any],
                    let statusesArray = dict["statuses"] as? [[String: Any]] {
                    // 拿到服务器返回的微博数据
                    // 4.网络请求数据回来,保存到数据库中
                    self.saveStatus(statuses: statusesArray)
                    
                    // 5.把网络数据返回回去
                    completion(statusesArray, nil)
                }
                
                }, failure: { (_, error) in
                    completion(nil, error)
            })
        }
    }
    
    
    /// 加载本地数据库中的微博数据
    ///
    /// - parameter since_id: since_id
    /// - parameter max_id:   max_id
    private func loadCacheStatus(since_id: Int64, max_id: Int64, completion: @escaping ([[String: Any]]?, Error?) -> Void) -> Void {
        // 用户的id
        guard let uid = HMUserAccountVM.shared.userAccount?.uid else {
            print("没有uid")
            return
        }
        
        // sql
        var sql = "SELECT * FROM T_Status \n" +
        "WHERE userId = \(uid) \n"
        
        if since_id > 0 {
            sql += "AND statusId > \(since_id) \n"
        } else if max_id > 0 {
            sql += "AND statusId < \(max_id) \n"
        }
        
        sql += "ORDER BY statusId DESC LIMIT 20;"
        
        print("sql: \(sql)")
        
        // 查询数据
        HMSQLiteManager.shared.dbQueue.inDatabase { (db) in
            do {
                let resultSet = try db!.executeQuery(sql, values: [])
                
                // 微博字典数组
                var statuses = [[String: Any]]()
                
                // 获取查询的结果
                while resultSet.next() {
                    // 获取微博字典
                    let string = resultSet.string(forColumn: "status")
                    
                    // 字符串 -> Data -> 字典(JSON)
                    let data = string?.data(using: .utf8)
                    let dict = try JSONSerialization.jsonObject(with: data!, options: []) as! [String: Any]
                    
                    statuses.append(dict)
                }
                
                completion(statuses, nil)
            } catch let error as NSError {
                print("查询微博数据失败了: \(error)")
                completion(nil, error)
            }
        }
    }
    
    
    /// 保存微博数据到数据库中
    ///
    /// - parameter statuses: 微博数据
    private func saveStatus(statuses: [[String: Any]]) -> Void {
        // 用户的id
        guard let uid = HMUserAccountVM.shared.userAccount?.uid else {
            print("没有uid")
            return
        }
        
        HMSQLiteManager.shared.dbQueue.inTransaction { (db, rollback) in
            // sql
            // INSERT OR REPLACE INTO: 添加或替换数据,当主键一样的时候,就替换,这个主键不存在就直接添加
            let sql = "INSERT OR REPLACE INTO T_Status (statusId, status, userId) VALUES (?, ?, \(uid));"
            
            do {
                // 遍历微博数据
                for status in statuses {
                    // 注意: 字典的值直接转Int64失败,转成NSNumber
                    let statusId = status["id"] as! NSNumber
                    
                    // 字典转成字符串: 字典(JSON) -> Data -> 字符串
                    let data = try JSONSerialization.data(withJSONObject: status, options: [])
                    let statusString = String(data: data, encoding: .utf8)!
                    
                    // 添加一条微博到数据库
                    try db?.executeUpdate(sql, values: [statusId, statusString])
                }
                
            } catch let error as NSError {
                print("添加微博失败了: \(error)")
            }
            print("添加了 \(statuses.count) 条微博数据")
        }
    }
}
